/**   
 * Copyright © 2019 eSunny Info. Tech Ltd. All rights reserved.
 * 
 * @Description:TODO(功能)
 * @className CategorysServiceImpl.java
 * @Package: com.erp.service 
 * @author: yj   
 * @date: 2019年1月17日 上午9:48:22 
 */
package com.erp.service;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.erp.dao.AdminDao;
import com.erp.dao.AdminDaoImpl;
import com.erp.dao.CategorysDaoImpl;
import com.erp.dao.ProductsDaoImpl;
import com.erp.po.Categorys;
import com.erp.util.DBHelper;
import com.erp.util.Page;

/**
 * @className CategorysServiceImpl.java
 * @author yj
 * @Description:TODO(功能)
 * @Date 2019年1月17日
 */
public class CategorysServiceImpl implements CategorysService {
	
	
	private CategorysDaoImpl cd;
	private ProductsDaoImpl pd;
	
	public CategorysServiceImpl() {
		cd = new CategorysDaoImpl();
		pd = new ProductsDaoImpl();
	}
	
	/* (管理员 查询 Categorys <类别表> )
	 * 	
	 */
	@Override
	public Page<Categorys> findCategorysListByPage(Page<Categorys> page) {
		Connection conn = null;
		ResultSet set = null;
		ResultSet set2 = null;
		int total = 0;
		ArrayList<Categorys> list = new ArrayList<Categorys>();

		try {
			conn = DBHelper.getConnection();
			
			set2 = cd.selectCategorysByTotal(conn);
			while (set2.next()) {
				total = set2.getInt(1);
			}
			
			set = cd.selectCategorysByPage(conn,page);
			while (set.next()) {
				int categoryId = set.getInt("categoryID");
				String categoryName = set.getString("category_name");
				String categoryDesc = set.getString("category_desc");

				list.add(new Categorys(categoryId, categoryName, categoryDesc));
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			cd.close();
		}
		page.setList(list);
		page.setTotal(total);
		
		return page;
	}

	/* (管理员 增加类别) (查询 (是否有添加重名的商品))
	 * 
	 */
	@Override
	public int addCategorys(Categorys categorys) {
		//boolean f = false;
		Connection conn = null;
		int code = 0; // 300: 有空串     |  301: 重复值       | 302: 成功
		
		if (categorys == null || categorys.getCategoryName().equals("") || categorys.getCategoryName().equals(" ")
				|| categorys.getCategoryDesc().equals("") || categorys.getCategoryDesc().equals(" ")) {
			code = 300;
			return code;
		}
		
		try {
			conn = DBHelper.getConnection();
			
			// 查询 (是否有添加重名的商品)
			ResultSet set = cd.selectCategorysByInsert(conn, categorys);
			if (set.next()) {
				// return false 添加失败
				//f = false;
				code = 301;
			} else {
				// 事务处理
				conn.setAutoCommit(false);
				
				int i = cd.insertCategorys(conn, categorys);
				conn.commit();
				conn.setAutoCommit(true);
				if (i > 0) {
					//f = true;
					code = 302;
				} else {
					//f = false;
					code = 301;
				}
			}
			
		} catch (SQLException e) {
			try {
				conn.rollback();
				conn.setAutoCommit(true);
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			
			e.printStackTrace();
		} finally {
			cd.close();
		}
		return code;
	}

	/* (修改 ( 修改 类别 名称 ,简介 )  / 针对本 id 的名称不修改,不构成重名 )
	 * 
	 */
	@Override
	public int modifyCategorys(Categorys categorys) {
		//boolean f = false;
		Connection conn = null;
		int code = 0; // 300: 有空串     |  301: 重复值       | 302: 成功     |  303: 没有该类别
		if (categorys == null || categorys.getCategoryName().equals("") || categorys.getCategoryName().equals(" ")
				|| categorys.getCategoryDesc().equals("") || categorys.getCategoryDesc().equals(" ")) {
			code = 300;
			return code;
		}
		
		try {
			conn = DBHelper.getConnection();
			
			// 查询 (针对本 id 的名称不修改,不构成重名)
			ResultSet set = cd.selectCategorysByInsert(conn, categorys);
//			if (!set.next()) {
//				code = 303;
//				return code;
//			}
			if (set.next()) {
				
				int findId = set.getInt(1);
				if (!(categorys.getCategoryId() == findId)) {
					code = 301;
					return code;
				}
			}

			// 事务处理
			conn.setAutoCommit(false);
			int i = cd.updateCategorysById(conn, categorys);
			conn.commit();
			conn.setAutoCommit(true);
			if (i > 0) {
				// f = true;
				code = 302;
			} else {
				// f = false;
				code = 303;
			}
			
		} catch (SQLException e) {
			try {
				conn.rollback();
				conn.setAutoCommit(true);
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		} finally {
			cd.close();
		}

		return code;
	}

	/* (删除 ( 删除 类别 ) -- 外键校验 )
	 * 
	 */
	@Override
	public int removeCategorys(int categoryId) {
		boolean f = false;
		Connection conn = null;
		int code = 0;   // 300: 商品不为空        |  301: 没有该供应商      |  302: 成功
		
		try {
			conn = DBHelper.getConnection();
			
			// -- 外键校验 
			ResultSet set = pd.selectCategorysByFkCheck(conn, categoryId);
			while (set.next()) {
				int count = set.getInt(1);
				//System.out.println(count);
				if (count > 0) {
					code = 300;
					//f = false;
				} else {
					
					//事务处理
					conn.setAutoCommit(false);
					
					// 确定无外键联系 可以删除
					int i = cd.deleteCategorysById(conn, categoryId);
					conn.commit();
					conn.setAutoCommit(true);
					if (i > 0) {
						code = 302;
						//f = true;
					} else {
						code = 301;
						//f = false;
					}
				}
			}
		} catch (SQLException e) {
			try {
				conn.rollback();
				conn.setAutoCommit(true);
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		} finally {
			cd.close();
		}
		
		return code;
	}
	
	

	/* (查询 category)
	 * 
	 */
	@Override
	public List<Categorys> findCategorys() {

		Connection conn = null;
		ResultSet set = null;
		ArrayList<Categorys> list = new ArrayList<Categorys>();
		try {
			conn = DBHelper.getConnection();
			set = cd.selectCategorys(conn);
			while (set.next()) {
				int categoryId = set.getInt("categoryID");
				String categoryName = set.getString("category_name");
				String categoryDesc = set.getString("category_desc");

			 	list.add(new Categorys(categoryId, categoryName, categoryDesc));
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			cd.close();
		}

		return list;
	}

	
	/* (根据id查询 category)
	 * 
	 */
	@Override
	public Categorys findCategoryById(int id) {
		Connection conn = null;
		ResultSet set = null;
		
		try {
			conn = DBHelper.getConnection();
			set = cd.selectCategoryById(conn, id);
			if (set.next()) {
				int categoryId = set.getInt("categoryID");
				String categoryName = set.getString("category_name");
				String categoryDesc = set.getString("category_desc");

			 	return new Categorys(categoryId, categoryName, categoryDesc);
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			cd.close();
		}
		return null;
	}

	/**
	 * 根据名称查询 category
	 */
	@Override
	public boolean findCategoryByName(String cateName) {
		Connection conn = null;
		ResultSet set = null;
		
		try {
			conn = DBHelper.getConnection();
			
			set = cd.selectCategoryByName(conn, cateName);
			if(set.next()) {
				return false;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			cd.close();
		}
			
		return true;
	}

	/**
	 * id相同,名称不修改不构成重名
	 */
	@Override
	public Categorys findCategoryByPName(String cateName) {
		Connection conn = null;
		ResultSet set = null;
		
		try {
			conn = DBHelper.getConnection();
			
			set = cd.selectCategoryByName(conn, cateName);
			if(set.next()) {
				int categoryId = set.getInt("categoryID");
				
				return new Categorys(categoryId);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			cd.close();
		}
			
		return null;
	}

	/* 
	 * 根据条件查询 Categorys <类别表> ( 分页 )
	 */
	@Override
	public Page<Categorys> findCategorysListByCondition(Page<Categorys> page, Categorys cate) {
		Connection conn = null;
		ResultSet set = null;
		ResultSet set2 = null;
		int total = 0;
		ArrayList<Categorys> list = new ArrayList<Categorys>();

		try {
			conn = DBHelper.getConnection();
			
			set2 = cd.selectConditionByTotal(conn, cate);
			while (set2.next()) {
				total = set2.getInt(1);
			}
			
			set = cd.selectCategorysByCondition(conn, cate, page);
			while (set.next()) {
				int categoryId = set.getInt("categoryID");
				String categoryName = set.getString("category_name");
				String categoryDesc = set.getString("category_desc");

				list.add(new Categorys(categoryId, categoryName, categoryDesc));
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			cd.close();
		}
		page.setList(list);
		page.setTotal(total);
		
		return page;
	}

}
